using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Common;
using System.Data;

namespace Musicman.DAL
{
    public static class PendingDownloadsDAO
    {

        private static DbCommand insertCommand = null;
        private static DbCommand deleteCommand = null;
        private static DbCommand updateDownloadStat = null;

        private static string deleteFileSql = "DELETE FROM USERDOWNLOADS WHERE UserID = @UserID AND SearchFielID = @SearchFileID";
        private static string updateStatsProcName = "musicman.dbo.UpdateDownloadStats";

        private static DbCommand GetUpdateCommand(SqlDatabase db)
        {
            if (insertCommand == null)
            {
                insertCommand = db.GetStoredProcCommand("AddPendingDownload");
                db.AddInParameter(insertCommand, "@UserID", DbType.Int64);
                db.AddInParameter(insertCommand, "@SearchFileID", DbType.Int64);
            }
            return insertCommand;
        }

        private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, long userID, long searchFileID)
        {
            db.SetParameterValue(cmd, "@UserID", userID);
            db.SetParameterValue(cmd, "@SearchFileID", searchFileID);
        }

        private static DbCommand GetUpdateStatsCommand(SqlDatabase db)
        {
            if (updateDownloadStat == null)
            {
                updateDownloadStat = db.GetStoredProcCommand(updateStatsProcName);
                db.AddInParameter(updateDownloadStat, "@PersonID", DbType.Int64);
                db.AddInParameter(updateDownloadStat, "@SearchFileID", DbType.Int64);
                db.AddInParameter(updateDownloadStat, "@num", DbType.Int32);
            }
            return updateDownloadStat;
        }

        private static void SetUpdateStatsValues(SqlDatabase db, DbCommand cmd, long personID, long fileID, int num)
        {
            db.SetParameterValue(cmd, "@PersonID", personID);
            db.SetParameterValue(cmd, "@SearchFileID", fileID);
            db.SetParameterValue(cmd, "@num", num);
        }

        private static DbCommand getdeleteCommand(SqlDatabase db)
        {
            if (deleteCommand == null)
            {
                deleteCommand = db.GetSqlStringCommand(deleteFileSql);
                db.AddInParameter(deleteCommand, "@UserID", DbType.Int64);
                db.AddInParameter(deleteCommand, "@SearchFileID", DbType.Int64);
            }
            return deleteCommand;
        }

        public static bool AddPendingDownloadsList(long userID, List<long> Files)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = GetUpdateCommand(db);
                db.SetParameterValue(cmd, "@UserID", userID);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();

                for (int i = 0; i < Files.Count; i++)
                {
                    db.SetParameterValue(cmd, "@SearchFileID", Files[i]);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }

        public static bool RemovePendingDownload(long userID, long fileID, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = true;
            DbCommand cmd = getdeleteCommand(db);
            SetUpdateValues(db, cmd, userID, fileID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            return res;
        }

        public static bool UpdateDownloadStats(long personID, long searchFirleID, int num, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = true;
            DbCommand cmd = GetUpdateStatsCommand(db);
            SetUpdateStatsValues(db, cmd, personID, searchFirleID, num);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            return res;
        }


        public static bool UpdateDownloadStats(long personID ,long searchFirleID, int num)
        {
            bool res = true;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetUpdateStatsCommand(db);
            SetUpdateStatsValues(db, cmd, personID, searchFirleID, num);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
    }
}
